﻿CREATE PROCEDURE [dbo].[users_get_listing]
    @page int = 0,
    @size int = 0,
    @column nvarchar(256),
    @direction int = 0,
    @email nvarchar(256) = null,
    @login nvarchar(256) = null
AS
    SELECT
            UserId,
            Email,
            [Login]
    FROM Users
    where 
        (@email is NULL OR Users.Email like '%'+ @email + '%')
        AND
        (@login is NULL OR Users.Login like '%'+ @login + '%')
    ORDER BY
        CASE WHEN @column = 'Email' AND @direction = 1 THEN Users.Email END DESC,
        CASE WHEN @column = 'Email' AND @direction = 0 THEN Users.Email END ASC,
        CASE WHEN @column = 'Login' AND @direction = 1 THEN Users.Login END DESC,
        CASE WHEN @column = 'Login' AND @direction = 0 THEN Users.Login END ASC
    OFFSET ((@page-1)*@size) ROWS
    FETCH NEXT @size ROWS ONLY;

Declare 
@TotalCount int

 SELECT @TotalCount = Count(DISTINCT Users.UserId)
    FROM Users
    where 
    (@email is NULL OR Users.Email like '%'+ @email + '%')
    AND
    (@login is NULL OR Users.Login like '%'+ @login + '%')

RETURN @TotalCount;